﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;


namespace DBETLTools
{
     
    class DBToSql
    {
        public String T1 = "tb_emp7";
        //创建触发器记录表
        public String CREATET1( )
        {
            
  

            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.Append("CREATE TABLE `" + T1+ "` (");
            stringBuilder.Append("`id` bigint UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',");
            stringBuilder.Append("`t` varchar(255) NULL COMMENT '表明',");
            stringBuilder.Append(" `p` varchar(255) NULL COMMENT '主键',");
            stringBuilder.Append("`v` varchar(255) NULL COMMENT '主键值',");
            stringBuilder.Append("`m` varchar(255) NULL COMMENT '操作',");
            stringBuilder.Append("PRIMARY KEY(`id`)");
            stringBuilder.Append("); ");
          

            return stringBuilder.ToString();
        }

        public string Update(DataRow dataRow,string key )
        {
            Type[] types = { typeof(UInt16), typeof(UInt32), typeof(UInt64), typeof(Int16), typeof(Int32), typeof(Int64) };
            StringBuilder Sql = new StringBuilder();
            Sql.Append("Update  " + dataRow.Table.TableName);
            Sql.Append(" set ");
            StringBuilder SqlS = new StringBuilder();
            
            foreach (DataColumn  item in dataRow.Table.Columns)
            {

                if(item.ColumnName!= key)
                {
                    if (dataRow[item.ColumnName].ToString().Length > 0)
                    {
                        if (types.Contains<Type>(dataRow[item.ColumnName].GetType()))
                        {
                            SqlS.Append(item.ColumnName + "="  + dataRow[item.ColumnName].ToString() + ",");
                        }
                        else
                        {
                            if (dataRow[item.ColumnName].GetType() == typeof(DateTime))
                            {
                                SqlS.Append(item.ColumnName + "='" + ((DateTime)dataRow[item.ColumnName]).ToString("yyyy-MM-dd hh:mm:ss") + "',");
                            }
                            else
                            {
                                SqlS.Append(item.ColumnName + "='" + dataRow[item.ColumnName].ToString() + "',");
                            }
                        }
                        
                    }
                }
            }
            //移除最后一个 ,
            SqlS.Remove(SqlS.Length-1, 1);
            Sql.Append(SqlS);
            Sql.Append(" where " + key + "=" + "'" + dataRow[key].ToString() + "'");
            return Sql.ToString();
        }
        public String Insert(DataRow dataRow)
        {
            Type[] types = { typeof(UInt16), typeof(UInt32),typeof(UInt64),typeof(Int16), typeof(Int32), typeof(Int64) };
            StringBuilder Sql = new StringBuilder();
            Sql.Append("INSERT INTO "   + dataRow.Table.TableName);
            
            StringBuilder SqlS = new StringBuilder();
            StringBuilder Sqlv = new StringBuilder();
            SqlS.Append(" ( ");
            Sqlv.Append(" ( ");
            foreach (DataColumn item in dataRow.Table.Columns)
            {
                if (dataRow[item.ColumnName].ToString().Length>0 )
                {
                    SqlS.Append(item.ColumnName + ",");
                    

                    if ( types.Contains<Type>(dataRow[item.ColumnName].GetType()))
                    {
                        Sqlv.Append(    dataRow[item.ColumnName].ToString() + ",");
                    }else
                    {
                        if (dataRow[item.ColumnName].GetType() == typeof(DateTime))
                        {
                            Sqlv.Append("'" + ((DateTime)dataRow[item.ColumnName]).ToString("yyyy-MM-dd hh:mm:ss") + "',");
                        }
                        else
                        {
                            Sqlv.Append("'" + dataRow[item.ColumnName].ToString() + "',");
                        }
                    }
                }
            }
            //移除最后一个 ,
            SqlS.Remove(SqlS.Length-1, 1);
            Sqlv.Remove(Sqlv.Length-1, 1);
            Sqlv.Append(")");
            //VALUES 
            Sql.Append(SqlS);
            Sql.Append(") VALUES " +Sqlv);

            return Sql.ToString();
        }
        public String Delete(string TableName,string PrimaryKey ,string v)
        {
            return " DELETE FROM  " + TableName + " where " + PrimaryKey + "='" + v + "'";
        }

        public String Mysqlinserttrigger(string Tibabname,string Key="")
        {
            StringBuilder Sql = new StringBuilder();
            //创建的触发器名字
            Sql.Append("CREATE  TRIGGER " + Tibabname+"_inst_" +DateTime.Now.ToString("yyyyMMdd"));
            //触发写入前 还是写入后
            Sql.Append(" AFTER  ");
            //复发状态
            
            Sql.Append(" INSERT ");
          
            Sql.Append(" ON " + Tibabname + " FOR EACH Row ");
          
            //触发以后的操作
            Sql.Append(" INSERT INTO  " +T1);
            Sql.Append(" (t,p,v,m) VALUES ('"+Tibabname+"','" + Key+"',NEW."+Key+",'INSERT')");
            
            return Sql.ToString();

        }
        public String MysqlDELETEtrigger(string Tibabname, string Key = "")
        {
            StringBuilder Sql = new StringBuilder();
            //创建的触发器名字
            Sql.Append("CREATE TRIGGER " + Tibabname + "_delete_" + DateTime.Now.ToString("yyyyMMdd"));
            //触发写入前 还是写入后
            Sql.Append(" BEFORE  ");
            //复发状态
           
            
            Sql.Append(" DELETE  ");
            Sql.Append(" ON " + Tibabname + " FOR EACH Row ");
           
            //触发以后的操作
            Sql.Append(" INSERT INTO  " + T1);
            Sql.Append("(t,p,v,m) VALUES ('" + Tibabname + "','" + Key + "',OLD." + Key + ",'DELETE')");
         
            return Sql.ToString();

        }
        public String MysqlUPDATEtrigger(string Tibabname, string Key = "")
        {
            StringBuilder Sql = new StringBuilder();
            //创建的触发器名字
            Sql.Append("CREATE  TRIGGER " + Tibabname + "_update_" + DateTime.Now.ToString("yyyyMMdd"));
            //触发写入前 还是写入后
            Sql.Append(" AFTER  ");
            //复发状态
            
            Sql.Append(" UPDATE ");
           
            Sql.Append(" ON " + Tibabname + " FOR EACH Row ");
          
            //触发以后的操作
            Sql.Append(" INSERT INTO  " + T1);
            Sql.Append(" (t,p,v,m) VALUES  ('" + Tibabname + "','" + Key + "',NEW." + Key + ",'UPDATE')");
             
            return Sql.ToString();

        }

    }
}
